package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60101 extends Utility{
	public String url = "/apps/acc6/ACC60101A.jsp";
	
	public ACC60101(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		}else if(job.equals("add")){
			this.add(request);
		}else if(job.equals("save")){
			this.save(request);
		}else if(job.equals("update")){
			this.update(request);
		}else if(job.equals("saveEdit")){
			this.saveEdit(request);
		}else if(job.equals("manager")){
			this.manager(request);
		}else if(job.equals("saveParent")){
			this.saveParent(request);
		}else if(job.equals("rollBack")){
			this.rollBack(request);
		}else if(job.equals("saveNagative")){
			this.saveNagative(request);
		}else if(job.equals("back")){
			this.init(request);
		}
	}
	@SuppressWarnings("unchecked")
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String sql = "";
			String table = this.schema + ".ACC6_ALLOCATE";
			List<Map<String, String>> list = null;

			sql = "SELECT DISTINCT BUDGET_YEAR FROM "+table+" ORDER BY BUDGET_YEAR DESC";
			list = dao.listDataSql(sql);
			request.setAttribute("listData", list);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String add(HttpServletRequest request){

		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer strBuffer = new StringBuffer();
			Map map = new HashMap();
			List list = new ArrayList();
			String budgetYearForCheck = "";
			
			strBuffer.append("SELECT DISTINCT BUDGET_YEAR ");
			strBuffer.append("FROM ACC6_ALLOCATE ");
			list = (ArrayList)dao.listDataSql(strBuffer.toString());
			for(int i=0;i<list.size();i++){
				map = (Map)list.get(i);
				budgetYearForCheck += map.get("BUDGET_YEAR").toString()+",";
			}
			request.setAttribute("budgetYear", this.dateUtil().getBudgetYear());
			request.setAttribute("budgetYearForCheck", budgetYearForCheck);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60101B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
	}
	@SuppressWarnings("unchecked")
	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			
			String sql = "";
			String table = this.schema + ".ACC6_ALLOCATE";
			String budgetYear = this.getAttr(request, "budgetYear");
			String[] code = request.getParameterValues("code");
			String[] sapCode = request.getParameterValues("sapCode");
			String[] allocateName = request.getParameterValues("allocateName");
			Map<String, String> map = null;
			
			sql = "SELECT ALLOCATE_CODE FROM "+table+" WHERE ALLOCATE_CODE = "+budgetYear;
			map =  dao.findDataSql(sql);
			if( map.get("ALLOCATE_CODE") == null || map.get("ALLOCATE_CODE").equals("")){
				allocateBean.setBudgetYear(budgetYear);
				allocateBean.setAllocateCode(budgetYear);
				allocateBean.setSapCode(budgetYear);
				allocateBean.setAllocateName("รหัสแจ้งจัดสรรเงินให้คู่สัญญา "+budgetYear.substring(2));
				allocateBean.setAllocateLevel("0");
				allocateBean.setAllocateSeq("0");
				allocateBean.setParentCode("0");
				allocateBean.setCreatedBy(this.user(request).getUserId());
				allocateBean.setUpdatedBy(this.user(request).getUserId());
				dao.insert(allocateBean);
			}
			if(code.length > 0 && sapCode.length > 0 && allocateName.length > 0){
				for(int i=0;i<code.length;i++){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setAllocateCode(code[i]);
					allocateBean.setSapCode(sapCode[i]);
					allocateBean.setAllocateName(this.encodeUTF(allocateName[i]));
					allocateBean.setCreatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					dao.insert(allocateBean);
				}
			}
			conn.commit();
			this.manager(request);
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	@SuppressWarnings("unchecked")
	private String update(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String sql = "";
			String allocateStr = "";
			String table = this.schema + ".ACC6_ALLOCATE";
			String budgetYear = this.getAttr(request, "budgetYear");
			List<Map<String, String>> list = null;
			Map map = new HashMap();
			
			sql = "SELECT ALLOCATE_CODE,SAP_CODE,ALLOCATE_NAME,PARENT_CODE,ALLOCATE_LEVEL FROM "+table+" WHERE BUDGET_YEAR = '"+budgetYear+"'"
			+ " AND ALLOCATE_CODE != "+budgetYear.substring(2)+"000000 AND ACTIVE = 'Y' ORDER BY ALLOCATE_SEQ ASC";
			list = dao.listDataSql(sql);
			if(list != null && list.size() > 0 ){
				for(int i = 0;i<list.size();i++){
					map =  list.get(i);
					allocateStr += map.get("ALLOCATE_CODE")+",";
				}
			}
			allocateStr = allocateStr.substring(0, allocateStr.length()-1);
			request.setAttribute("allocateStr", allocateStr);
			request.setAttribute("listData", list);
			request.setAttribute("sizeData", list.size());
			request.setAttribute("budgetYear", budgetYear);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60101B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	@SuppressWarnings({ "unchecked"})
	private String manager(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String sql = "";
			List<Map<String, String>> list = new ArrayList<Map<String,String>>();
			String table = this.schema + ".ACC6_ALLOCATE";
			Map<String, String> map = null;
			String budgetYear = this.getAttr(request, "budgetYear");
			
			sql = "select ALLOCATE_CODE,ALLOCATE_NAME,BUDGET_YEAR,PARENT_CODE,ALLOCATE_LEVEL,NEGATIVE,ALLOCATE_SEQ,ALLOCATE_APPLY "
				+ "FROM "+table+" "
				+ "WHERE ALLOCATE_SEQ IS NOT NULL AND ALLOCATE_CODE IS NOT NULL AND PARENT_CODE IS NOT NULL AND ACTIVE = 'Y' AND BUDGET_YEAR = '"+budgetYear+"' ORDER BY ALLOCATE_SEQ ASC";
			list = dao.listDataSql(sql);
			request.setAttribute("listData", list);
			
			map = list.get(0);
			sql = "SELECT ALLOCATE_CODE,SAP_CODE,ALLOCATE_NAME,BUDGET_YEAR FROM "+table+" WHERE BUDGET_YEAR = '"+budgetYear+"' "
			+ "AND ALLOCATE_CODE != "+map.get("BUDGET_YEAR").substring(2)+"000000 AND ACTIVE = 'Y' "
			+ "AND PARENT_CODE IS NULL AND ALLOCATE_LEVEL IS NULL ORDER BY ALLOCATE_CODE ASC";
			list = null;
			list = dao.listDataSql(sql);
			request.setAttribute("listDataDialog", list);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("negativeAllocate", this.properties().negativeAllocate());
			this.url = "/apps/acc6/ACC60101C.jsp";
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String saveEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			String budgetYear = this.getAttr(request, "budgetYear");
			String listCode = this.getAttr(request, "listCode");
			String[] code = request.getParameterValues("code");
			String[] sapCode = request.getParameterValues("sapCode");
			String[] allocateName = request.getParameterValues("allocateName");
			String[] actionType = request.getParameterValues("actionType");
			String[] arrStr = null;
			//String sql = "";
			//delete statement
			arrStr = listCode.split("\\,");
			if(arrStr.length > 0){
				for(int i=0;i<arrStr.length;i++){
					if(arrStr[i] != null && !arrStr[i].equals("")){
						allocateBean = new Acc6AllocateBean();
						allocateBean.setActive("N");
						allocateBean.setBudgetYear(budgetYear);
						allocateBean.setAllocateCode(arrStr[i]);
						allocateBean.setUpdatedBy(this.user(request).getUserId());
						allocateBean.setUpdatedDate(this.sysDate);
						dao.update(allocateBean);
					}
				}
			}
			for(int j=0;j<code.length;j++){
				if(actionType[j].equals("I")){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setAllocateCode(code[j]);
					allocateBean.setSapCode(sapCode[j]);
					allocateBean.setAllocateName(this.encodeUTF(allocateName[j]));
					allocateBean.setCreatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					dao.insert(allocateBean);
				}else if(actionType[j].equals("U")){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setAllocateCode(code[j]);
					allocateBean.setSapCode(sapCode[j]);
					allocateBean.setAllocateName(this.encodeUTF(allocateName[j]));
					allocateBean.setUpdatedDate(this.sysDate);
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					dao.update(allocateBean);
				}
			}
			request.setAttribute("budgetYear", budgetYear);
			this.message(request).setMsgCode("I002");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.update(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String saveParent(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			StringBuffer sql = new StringBuffer();
			Map map = new HashMap();
			List list = new ArrayList();
			String[] childId = request.getParameterValues("childId");
			String parentCode = this.getAttr(request, "parentCode");
			String parentLevel = this.getAttr(request, "parentLevel");
			String budgetYear = this.getAttr(request, "budgetYear");
			String allocateSeq = this.getAttr(request, "allocateSeq");
			
			int parentLevelInt = 0;
			int allocateSeqInt = 0;
			int maxSeq = 0;
			
			if(parentLevel == null || parentLevel.equals("")){
				parentLevelInt = 0;
			}else{
				parentLevelInt = Integer.parseInt(parentLevel)+1;
			}
			
			if(parentLevel.equals("0")){
				sql.append("SELECT MAX(ALLOCATE_SEQ) AS MAX_SEQ ");
				sql.append("FROM ACC6_ALLOCATE ");
				sql.append("WHERE BUDGET_YEAR = '"+budgetYear+"' ");
				map = dao.findDataSql(sql.toString());
				allocateSeqInt = Integer.parseInt(dao.chkData(map.get("MAX_SEQ").toString()))+1;
				for(int j=0;j<childId.length;j++){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setAllocateCode(childId[j]);
					allocateBean.setAllocateSeq(String.valueOf(allocateSeqInt+j));
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setParentCode(parentCode);
					allocateBean.setAllocateLevel(String.valueOf(Integer.parseInt(parentLevel)+1));
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					allocateBean.setAllocateApply("N");
					dao.update(allocateBean);
				}
				this.message(request).setMsgCode("I002");
				this.manager(request);
				
			}else {
				maxSeq = Integer.parseInt(allocateSeq);
				sql.delete(0, sql.length());
				sql.append("SELECT ALLOCATE_CODE,ALLOCATE_SEQ ");
				sql.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sql.append("WHERE BUDGET_YEAR = '"+budgetYear+"' AND ALLOCATE_SEQ > '"+(maxSeq)+"' ORDER BY ALLOCATE_SEQ ASC");
				maxSeq = maxSeq + 1;
				list = dao.listDataSql(sql.toString());
				for(int j=0;j<childId.length;j++){
					allocateBean = new Acc6AllocateBean();
					allocateBean.setAllocateCode(childId[j]);
					allocateBean.setAllocateSeq(String.valueOf(maxSeq));
					allocateBean.setParentCode(parentCode);
					allocateBean.setAllocateLevel(String.valueOf(Integer.parseInt(parentLevel)+1));
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					allocateBean.setAllocateApply("N");
					if(this.properties().sysBudgetYear() >= 2556 && parentLevelInt == 3){
						allocateBean.setAllocateApply("Y");
						allocateBean.setNegative("1");
					}else if(this.properties().sysBudgetYear() < 2556 && parentLevelInt == 2){
						allocateBean.setAllocateApply("Y");
						allocateBean.setNegative("1");
					}
					dao.update(allocateBean);
					maxSeq = maxSeq+1;
				}
				for(int j=0;j<list.size();j++){
					map = (Map)list.get(j);
					allocateBean = new Acc6AllocateBean();
					allocateBean.setAllocateCode(map.get("ALLOCATE_CODE").toString());
					allocateBean.setAllocateSeq(String.valueOf(maxSeq));
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					dao.update(allocateBean);
					maxSeq = maxSeq+1;
				}

				this.message(request).setMsgCode("I002");
				this.manager(request);
			}
			request.setAttribute("budgetYear", budgetYear);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.manager(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String rollBack(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String code = this.getAttr(request, "code");
			String budgetYear = this.getAttr(request, "budgetYear");
			String allocateSeq = this.getAttr(request, "allocateSeq");
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			String sql = "";
			List list = null;
			Map map = null;

			allocateBean.setAllocateLevel("null");
			allocateBean.setParentCode("null");
			allocateBean.setAllocateCode(code);
			allocateBean.setBudgetYear(budgetYear);
			allocateBean.setAllocateApply("N");
			allocateBean.setNegative("0");
			allocateBean.setAllocateSeq("null");
			allocateBean.setUpdatedBy(this.user(request).getUserId());
			allocateBean.setUpdatedDate(this.sysDate);
			dao.update(allocateBean," OR PARENT_CODE = "+code);
			
			sql = "SELECT ALLOCATE_CODE,ALLOCATE_SEQ FROM "+this.schema+".ACC6_ALLOCATE WHERE ALLOCATE_SEQ > " + allocateSeq + " AND BUDGET_YEAR = "+ budgetYear + " ORDER BY ALLOCATE_SEQ ASC";
			list = dao.listDataSql(sql);
			int allocateSeqInt = 0;
			if (allocateSeq.equals("1")) {
				allocateSeqInt = 0;
			} else {
				allocateSeqInt = Integer.parseInt(allocateSeq)-1;
			}
			if(list.size() > 0 ){
				for(int j=0;j<list.size();j++){
					map = (Map) list.get(j);
					allocateBean = new Acc6AllocateBean();
					allocateBean.setAllocateSeq(String.valueOf(allocateSeqInt+1));
					allocateBean.setAllocateCode(map.get("ALLOCATE_CODE").toString());
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					dao.update(allocateBean);
					allocateSeqInt++;
				}
			}
			conn.commit();
			this.message(request).setMsgCode("I002");
			this.manager(request);
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String saveNagative(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			String[] nagativeCheckBox= request.getParameterValues("nagative");
			String[] applyCheckBox= request.getParameterValues("apply");
			String allocateCode = "";
			String nagative = "";
			String budgetYear = this.getAttr(request, "budgetYear");
			String[] strArr = null;
			
			allocateBean.setNegative("N");
			allocateBean.setAllocateApply("N");
			allocateBean.setBudgetYear(budgetYear);
			dao.update(allocateBean, " AND (1=1)");
			conn.commit();
			if(nagativeCheckBox != null){
				for (int i = 0; i < nagativeCheckBox.length; i++) {
					allocateBean = new Acc6AllocateBean();
					strArr = nagativeCheckBox[i].split("\\|");
					allocateCode = strArr[0];
					budgetYear = strArr[1];
					nagative = strArr[2];
					allocateBean.setNegative(nagative);
					allocateBean.setAllocateCode(allocateCode);
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					dao.update(allocateBean);
				}
				conn.commit();
			}
			if(applyCheckBox != null){
				for (int i = 0; i < applyCheckBox.length; i++) {
					allocateBean = new Acc6AllocateBean();
					strArr = applyCheckBox[i].split("\\|");
					allocateCode = strArr[0];
					budgetYear = strArr[1];
					allocateBean.setAllocateApply("Y");
					allocateBean.setAllocateCode(allocateCode);
					allocateBean.setBudgetYear(budgetYear);
					allocateBean.setUpdatedBy(this.user(request).getUserId());
					allocateBean.setUpdatedDate(this.sysDate);
					dao.update(allocateBean);
				}
				conn.commit();
			}
			this.message(request).setMsgCode("I002");
			this.manager(request);
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
